package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.inter.SchoolDaoInterface;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author tg.si@188.com
 * @project xg22
 * @date 2025/3/15
 */
public class SchoolDaoImpl implements SchoolDaoInterface {

    @Override
    public School find(Integer id) throws SQLException, BaseException {
        // 创建School类型的引用，暂不创建对象
        School desiredSchool = null;
        // 使用 try-with-resources 自动关闭资源
        try (Connection connection = JdbcHelper.getConn();
             PreparedStatement pstmt_select_id = connection.prepareStatement("SELECT * FROM bysj.school WHERE id = ?")) {

            pstmt_select_id.setInt(1, id);
            try (ResultSet resultSet_select_id = pstmt_select_id.executeQuery()) {
                // 如果表中存在id对应的记录，则获得表中的字段值，并创建对象
                if (resultSet_select_id.next()) {
                    String description = resultSet_select_id.getString("description");
                    String no = resultSet_select_id.getString("no");
                    String remarks = resultSet_select_id.getString("remarks");
                    desiredSchool = new School(id, description, no, remarks);
                }
            }
        }
        return desiredSchool;
    }

    @Override
    public Collection<School> findAll() throws SQLException, BaseException {
        // 创建集合类对象，用来保存所有记录代表的School对象
        Collection<School> desiredSchools = new TreeSet<>();
        // 使用 try-with-resources 自动关闭资源
        try (Connection connection = JdbcHelper.getConn();
             PreparedStatement pstmt_select_all = connection.prepareStatement("SELECT * FROM School ")) {

            try (ResultSet resultSet = pstmt_select_all.executeQuery()) {
                // 遍历resultSet
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String description = resultSet.getString("description");
                    String no = resultSet.getString("no");
                    String remarks = resultSet.getString("remarks");
                    // 以当前记录的各个字段值为参数，创建School对象
                    School desiredSchool = new School(id, description, no, remarks);
                    // 将当前记录代表的School对象添加到desiredSchools中
                    desiredSchools.add(desiredSchool);
                }
            }
        }
        return desiredSchools;
    }

    public boolean update(Connection connection, School school) throws SQLException {
        // 准备预编译的语句
        String sql_update = "UPDATE school SET description = ?, no = ?, remarks = ? WHERE id = ?";
        try (PreparedStatement pstmt_update = connection.prepareStatement(sql_update)) {
            pstmt_update.setString(1, school.getDescription());
            pstmt_update.setString(2, school.getNo());
            pstmt_update.setString(3, school.getRemarks());
            pstmt_update.setInt(4, school.getId());

            // 执行预编译语句，影响的行数保存在rowAffected中
            int rowAffected = pstmt_update.executeUpdate();
            return rowAffected > 0;
        }
    }

    public boolean add(Connection connection, School school) throws SQLException {
        // 准备预编译的语句
        String sql_insert = "INSERT INTO school (description, no, remarks) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt_update = connection.prepareStatement(sql_insert)) {
            pstmt_update.setString(1, school.getDescription());
            pstmt_update.setString(2, school.getNo());
            pstmt_update.setString(3, school.getRemarks());

            // 执行预编译语句，影响的行数保存在rowAffected中
            int rowAffected = pstmt_update.executeUpdate();
            return rowAffected > 0;
        }
    }

    public boolean delete(Integer id) throws SQLException {
        // 获取数据库连接
        try (Connection connection = JdbcHelper.getConn();
             PreparedStatement pstmt_delete = connection.prepareStatement("DELETE FROM school WHERE id = ?")) {

            pstmt_delete.setInt(1, id);

            // 执行预编译语句，影响的行数保存在rowAffected中
            int rowAffected = pstmt_delete.executeUpdate();
            return rowAffected > 0;
        }
    }

    public boolean delete(School school) throws SQLException {
        return this.delete(school.getId());
    }

    public static void main(String[] args) throws SQLException {
        SchoolDaoInterface schoolDao = new SchoolDaoImpl();
        System.out.println(schoolDao.find(2));
    }
}

